﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint.Administration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SharePoint;

namespace TST.SharePointObjects
{
    public class SharePointObjectsDatabase: SPDatabase
    {
        private const String DATABASETITLE = "SharePoint Objects database";
        private const String SharePointObjectsMenuFeatureID = "A2D3E4E5-34AB-4c2d-8BCE-44271AF94542";
        private VersionInfo _versionInfo;
        private SqlDataAdapter _adapter;
        private SqlConnection _connection;

        public SharePointObjectsDatabase()
        {
            _versionInfo = new VersionInfo(this);
        }

        public SharePointObjectsDatabase(String componentName, SPDatabaseServiceInstance databaseService) :
            base(componentName, databaseService)
        {
            _versionInfo = new VersionInfo(this);
        }

        public override void Provision()
        {
            base.Provision();
            FileInfo sqlFile = GetProvisioningSQL();
            if (sqlFile != null)
            {
                Dictionary<string, bool> options = new Dictionary<string, bool>();
                Provision(this.DatabaseConnectionString, sqlFile.FullName, options);
                SqlConnection.ClearPool(new System.Data.SqlClient.SqlConnection(this.DatabaseConnectionString));               
                VersionInfo.RegisterVersion(1);
            }
        }

        public override void Upgrade()
        {
            base.Upgrade();
            List<FileInfo> sqlFiles = GetUpgradeSQL();
            if (sqlFiles != null)
            {
                foreach (FileInfo file in sqlFiles)
                {
                    StreamReader reader = File.OpenText(file.FullName);
                    string sql = reader.ReadToEnd();
                    reader.Close();
                    RunSQLFile(sql);
                    VersionInfo.RegisterVersion(GetVersionForUpgradeFile(file));
                }
            }
        }

        private FileInfo GetProvisioningSQL()
        {
            foreach (SPFeatureDefinition feature in SPFarm.Local.FeatureDefinitions)
            {
                if (feature.Id == new Guid(SharePointObjectsMenuFeatureID))
                {
                    DirectoryInfo dir = new DirectoryInfo(feature.RootDirectory);
                    if (dir.Exists)
                    {
                        string mask = @"CreateDatabase.sql";
                        FileInfo[] files = dir.GetFiles(mask, SearchOption.AllDirectories);
                        if (files.Length == 1)
                        {
                            return files[0];
                        }
                    }
                }
            }
            return null;
        }

        private int GetVersionForUpgradeFile(FileInfo fileInfo)
        {
            int result = 0;
            String versionString = fileInfo.Name.Substring(fileInfo.Name.IndexOf("_") + 1);
            versionString = versionString.Substring(0, versionString.IndexOf("."));
            int version = 0;
            if (int.TryParse(versionString, out version))
            {
                result = version;
            }
            return result;
        }

        private List<FileInfo> GetUpgradeSQL()
        {
            List<FileInfo> result = new List<FileInfo>();
            foreach (SPFeatureDefinition feature in SPFarm.Local.FeatureDefinitions)
            {
                if (feature.Id == new Guid(SharePointObjectsMenuFeatureID))
                {
                    DirectoryInfo dir = new DirectoryInfo(feature.RootDirectory);
                    if (dir.Exists)
                    {
                        string mask = @"UpgradeDatabase_*.sql";
                        FileInfo[] files = dir.GetFiles(mask, SearchOption.AllDirectories);
                        foreach (FileInfo file in files)
                        {
                            if (GetVersionForUpgradeFile(file) > VersionInfo.DatabaseVersion)
                            {
                                result.Add(file);
                            }
                        }
                    }
                }
            }
            return result;
        }

        public VersionInfo VersionInfo
        {
            get
            {
                return _versionInfo;
            }
        }

        public override bool NeedsUpgrade
        {
            get
            {
                List<FileInfo> sqlFiles = GetUpgradeSQL();
                return sqlFiles.Count > 0;
            }
            set
            {
                base.NeedsUpgrade = value;
            }
        }

        internal static SharePointObjectsDatabase GetDatabase()
        {
            SharePointObjectsDatabase result = null;
            SPAdministrationWebApplication adminWeb = SPAdministrationWebApplication.Local;
            foreach (SPDatabase db in adminWeb.WebService.DefaultDatabaseInstance.Databases)
            {
                if (db is SharePointObjectsDatabase)
                {
                    result = (SharePointObjectsDatabase)db;
                }
            }
            return result;
        }

        internal DataTable GetData(SqlCommand command, CommandType commandType)
        {
            if (command == null || string.IsNullOrEmpty(command.CommandText))
                return null;

            DataSet data = new DataSet();
            SqlConnection connection = null;
            SqlDataAdapter adapter = null;

            try
            {
                DataTable result = null;
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    connection = new System.Data.SqlClient.SqlConnection(this.DatabaseConnectionString);
                    adapter = null;

                    command.Connection = connection;
                    command.CommandType = commandType;

                    connection.Open();
                    adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                    if (data.Tables.Count == 1)
                    {
                        result = data.Tables[0];
                    }
                }
                );
                return result;
            }
            catch (SqlException sqlEx)
            {
                TraceProvider.WriteError(sqlEx.Message, "Database.GetData");
                throw new SharePointObjectsException(sqlEx.Message);
            }
            catch (Exception ex)
            {
                TraceProvider.WriteError(ex.Message, "Database.GetData");
                throw ex;
            }
            finally
            {
                CloseAndDispose(adapter, connection);
            }
        }

        internal DataTable GetData(SqlCommand selectCommand, SqlCommand insertCommand, SqlCommand updateCommand, Boolean closeConnection)
        {
            if (selectCommand == null || string.IsNullOrEmpty(selectCommand.CommandText))
                return null;

            DataSet data = new DataSet();
            SqlConnection connection = null;
            SqlDataAdapter adapter = null;

            try
            {
                DataTable result = null;
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                connection = new System.Data.SqlClient.SqlConnection(this.DatabaseConnectionString);
                adapter = null;

                selectCommand.Connection = connection;
                insertCommand.Connection = connection;
                updateCommand.Connection = connection;

                connection.Open();
                adapter = new SqlDataAdapter(selectCommand);
                adapter.InsertCommand = insertCommand;
                adapter.UpdateCommand = updateCommand;
                adapter.Fill(data);
                if (data.Tables.Count == 1)
                {
                    result = data.Tables[0];
                }
                }
                );
                return result;
            }
            catch (SqlException sqlEx)
            {
                TraceProvider.WriteError(sqlEx.Message, "Database.GetData");
                throw new SharePointObjectsException(sqlEx.Message);
            }
            catch (Exception ex)
            {
                TraceProvider.WriteError(ex.Message, "Database.GetData");
                throw ex;
            }
            finally
            {
                if (closeConnection)
                {
                    CloseAndDispose(adapter, connection);
                }
                else
                {
                    _adapter = adapter;
                    _connection = connection;
                }
            }
        }

        private static void CloseAndDispose(SqlDataAdapter adapter, SqlConnection connection)
        {
            if (adapter != null)
            {
                adapter.Dispose();
            }
            if (connection != null)
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
                connection.Dispose();
            }
        }

        internal void RunStoredProcedure(SqlCommand command)
        {
            if (command == null || string.IsNullOrEmpty(command.CommandText))
                return;

            DataSet data = new DataSet();
            SqlConnection connection = null;
            SqlDataAdapter adapter = null;

            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    connection = new System.Data.SqlClient.SqlConnection(DatabaseConnectionString);
                    adapter = null;

                    command.Connection = connection;
                    command.CommandType = CommandType.StoredProcedure;

                    connection.Open();
                    command.ExecuteNonQuery();
                }
                );
                return;
            }
            catch (SqlException sqlEx)
            {
                TraceProvider.WriteError(sqlEx.Message, "Database.RunStoredProcedure");
                CloseAndDispose(adapter, connection);
                throw new SharePointObjectsException(sqlEx.Message);
            }
            catch (Exception ex)
            {
                TraceProvider.WriteError(ex.Message, "Database.RunStoredProcedure");
                CloseAndDispose(adapter, connection);
                throw ex;
            }
            finally
            {
                CloseAndDispose(adapter, connection);
            }
        }

        internal void RunSQLCommand(string sql)
        {
            if (string.IsNullOrEmpty(sql))
                return;

            SqlConnection connection = null;
            SqlDataAdapter adapter = null;

            try
            {
                connection = new System.Data.SqlClient.SqlConnection(DatabaseConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                TraceProvider.WriteError(sqlEx.Message, "Database.RunSQLCommand");
                throw new SharePointObjectsException(sqlEx.Message);
            }
            catch (Exception ex)
            {
                TraceProvider.WriteError(ex.Message, "Database.RunSQLCommand");
                throw ex;
            }
            finally
            {
                CloseAndDispose(adapter, connection);
            }
        }

        internal void RunSQLCommand(SqlCommand command)
        {
            if (command==null)
                return;

            SqlConnection connection = null;
            SqlDataAdapter adapter = null;

            try
            {
                connection = new System.Data.SqlClient.SqlConnection(DatabaseConnectionString);
                command.CommandType = CommandType.Text;
                command.Connection = connection;
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                TraceProvider.WriteError(sqlEx.Message, "Database.RunSQLCommand");
                throw new SharePointObjectsException(sqlEx.Message);
            }
            catch (Exception ex)
            {
                TraceProvider.WriteError(ex.Message, "Database.RunSQLCommand");
                throw ex;
            }
            finally
            {
                CloseAndDispose(adapter, connection);
            }
        }

        internal void RunSQLFile(string sql)
        {
            string[] statements = sql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string statement in statements)
            {
                if (RunStatement(statement.Replace(Environment.NewLine, string.Empty)))
                {
                    RunSQLCommand(statement);
                }
            }
        }

        private static bool RunStatement(string statement)
        {
            if (statement.ToLower().StartsWith("use "))
                return false;
            if (statement.ToLower().StartsWith("set "))
                return false;
            return true;
        }


        internal static SharePointObjectsDatabase Create()
        {
            SPAdministrationWebApplication adminWeb = SPAdministrationWebApplication.Local;
            return new SharePointObjectsDatabase(DATABASETITLE, adminWeb.WebService.DefaultDatabaseInstance);
        }


        internal int UpdateData(DataTable data)
        {
            try
            {
                return _adapter.Update(data);
            }
            finally
            {
                CloseAndDispose(_adapter, _connection);
                //_adapter.Dispose();
                //if (_connection.State == ConnectionState.Open)
                //{
                //    _connection.Close();
                //}
                //_connection.Dispose();
            }
        }

        internal void GrantLogin(string login)
        {
            //SqlCommand command = new SqlCommand("sp_grantlogin");
            //SqlParameter paramUser = new SqlParameter("@loginame", SqlDbType.NVarChar, 128);
            //paramUser.Value = login;
            //command.Parameters.Add(paramUser);
            //RunStoredProcedure(command);
            try
            {
                RunSQLCommand(String.Format("CREATE LOGIN [{0}] FROM WINDOWS;", login));

            }
            catch (SharePointObjectsException)
            {
                // user already exists. Ignore.
            }
            try
            {
                SqlCommand commandDB = new SqlCommand("sp_grantdbaccess");
                SqlParameter paramUserDB = new SqlParameter("@loginame", SqlDbType.NVarChar, 128);
                paramUserDB.Value = login;
                commandDB.Parameters.Add(paramUserDB);
                RunStoredProcedure(commandDB);
            }
            catch (SharePointObjectsException)
            {
                // user already exists. Ignore.
            }
        }

        private void AddToRole(string login, string role)
        {
            SqlCommand command = new SqlCommand("sp_addrolemember");

            SqlParameter paramUser = new SqlParameter("@membername", SqlDbType.NVarChar, 128);
            paramUser.Value = login;
            command.Parameters.Add(paramUser);

            SqlParameter paramRole = new SqlParameter("@rolename", SqlDbType.NVarChar, 128);
            paramRole.Value = role;
            command.Parameters.Add(paramRole);

            RunStoredProcedure(command);
        }

        private void GrantExecutePermissions(String procedureName, String login)
        {
            RunSQLCommand(String.Format("GRANT EXECUTE ON {0} TO [{1}];", procedureName, login));
        }

        private void GrantInsertPermissions(String tableName, String login)
        {
            RunSQLCommand(String.Format("GRANT INSERT ON {0} TO [{1}];", tableName, login));
        }

        internal void GrantReadWritePermissions(string login)
        {
            try
            {
                AddToRole(login, "db_datareader");

                GrantExecutePermissions("DeleteObjectReferencesById", login);
                GrantExecutePermissions("DeleteObjectReferences", login);
                GrantExecutePermissions("GetIndexObjects", login);
                GrantExecutePermissions("GetSiteObjects", login);
                GrantExecutePermissions("GetSiteInfo", login);
                GrantExecutePermissions("GetVersionInfo", login);
                GrantExecutePermissions("SetVersionInfo", login);
                GrantExecutePermissions("UpdateSiteInfo", login);
                GrantExecutePermissions("UpdateSiteObjects", login);

                GrantInsertPermissions("ObjectReferences", login);
            }
            catch (SharePointObjectsException)
            {
                // user already exists. Ignore.
            }
        }
    }
}
